const db = require('@/db/db-connection')
const { multipleColumnSet } = require('@/utils/common.util')
const { newRandomId, dateFormat } = require('@/utils/common.util')

class SitesModel {
  constructor () {
    this.tableName = 'sites'
  }

  /**
   * find sites by folderId
   * @param {*} folderId
   * @returns
   */
  async find (folderId) {
    try {
      const sql = `SELECT * FROM ${this.tableName} s WHERE folderId = ? ORDER BY createTime DESC`

      const result = await db.query(sql, [folderId])

      return result
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * find all user sites
   * @param {*} userId
   * @returns
   */
  async findList (userId) {
    try {
      const sql = `SELECT t.id, t.userId, t.name, t.url, t.folderId, t.siteDesc, t.icon, f.name AS folderName, f.isPrivate, u.username, u.nickname, u.avatar, 
      
      uct.userId IS NOT NULL AS isCollect

      FROM ${this.tableName} AS t

      INNER JOIN site_folder f ON f.id = t.folderId AND f.isPrivate = '0'

      LEFT JOIN user_collect_tool uct ON uct.toolId = t.id AND uct.userId = ?

      LEFT JOIN user AS u ON t.userId = u.id `

      const result = await db.query(sql, [userId])

      async function findCollectUsers (item) {
        const sql = 'SELECT user.avatar FROM user WHERE user.id IN (SELECT uct.userId FROM user_collect_tool uct WHERE uct.toolId = ?) LIMIT 0, 5'

        const data = await db.query(sql, [item.id])

        return data
      }

      const data = await Promise.all(result.map(item => {
        return findCollectUsers(item)
      }))

      result.forEach((item, idx) => {
        item.collecters = data[idx]
      })

      return result
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * add site
   * @param { String } name site name
   * @param { String } folderId site folder id
   * @param { String } url site url
   */
  async add ({ name, folderId, url, userId, siteDesc }) {
    try {
      const [id, createTime] = [newRandomId(), dateFormat(new Date())]

      const sql = `INSERT INTO ${this.tableName} (id, name, folderId, userId, url, siteDesc, createTime) VALUES(?, ?, ?, ?, ?, ?, ?)`

      await db.query(sql, [id, name, folderId, userId, url, siteDesc, createTime])

      return id
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * update site
   * @param { String } id
   * @param { String } name
   * @param { String } url
   */
  async update ({ id, name, url, siteDesc, icon }) {
    try {
      const updateTime = dateFormat(new Date())

      const { columnSet, values } = multipleColumnSet({ name, url, updateTime, siteDesc, icon })

      const sql = `UPDATE ${this.tableName} SET ${columnSet} WHERE id = ?`

      await db.query(sql, [...values, id])
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * find one site
   * @param {*} params
   */
  async findOne (params) {
    try {
      const { columnSet, values } = multipleColumnSet(params, ' AND ')

      const sql = `SELECT s.id, s.userId FROM ${this.tableName} s WHERE ${columnSet}`

      const result = await db.query(sql, values)

      return result[0]
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * find one tool
   */
  async findOneTool (toolId) {
    try {
      const sql = 'SELECT * FROM tool WHERE id = ?'

      const result = await db.query(sql, [toolId])

      return result[0]
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * delete site
   * @param { String } id
   */
  async delete (param) {
    try {
      const { columnSet, values } = multipleColumnSet(param, ' AND ')

      const sql = `DELETE FROM ${this.tableName} WHERE ${columnSet}`

      await db.query(sql, values)
    } catch (error) {
      throw new Error(error)
    }
  }
}

module.exports = new SitesModel()
